package com.hdculture.simple.expensemanager.premium;

import java.text.DecimalFormat;
import java.util.Calendar;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CoreDBAdapter{

	// Database fields
	public static final String KEY_ROWID = "_id";
	public static final String INCOME_DT = "dt";
	public static final String INCOME_SOURCE = "source";
	public static final String INCOME_AMOUNT= "amount";
	public static final String INCOME_MONTH = "month";
	public static final String INCOME_YEAR = "year";
	public static final String EXPENSE_DT = "dt";
	public static final String EXPENSE_MONTH = "month";
	public static final String EXPENSE_YEAR = "year";
	public static final String EXPENSE_DESCRIPTION = "description";
	public static final String EXPENSE_AMOUNT="amount";
	private static final String INCOME_DATABASE_TABLE = "incomeData";
	private static final String EXPENSE_DATABASE_TABLE = "expenseData";
	private static final String SETTINGS_DATABASE_TABLE = "settings";
	public static final String SETTINGS_FIELDNAME="FieldName";
	public static final String SETTINGS_FIELDVALUE="FieldValue";
	
	private Context context;
	private SQLiteDatabase database;
	private CoreDBHelper dbHelper;

	public CoreDBAdapter(Context context) {
		this.context = context;
	}

	public CoreDBAdapter open() throws SQLException {
		dbHelper = new CoreDBHelper(context);
		database = dbHelper.getWritableDatabase();
		
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	public void createSettings(String passwordString, String currencySign){
		database.execSQL("DROP TABLE IF EXISTS settings");
		final String SETTINGS_DATABASE_CREATE = "create table settings (FieldName text not null, FieldValue text not null);";
		database.execSQL(SETTINGS_DATABASE_CREATE);
		ContentValues cv = new ContentValues();
		cv.put(SETTINGS_FIELDNAME, "PASSWORD");
		cv.put(SETTINGS_FIELDVALUE, passwordString);
		database.insert(SETTINGS_DATABASE_TABLE, null, cv);
		cv = new ContentValues();
		cv.put(SETTINGS_FIELDNAME, "CURRENCYSIGN");
		cv.put(SETTINGS_FIELDVALUE, currencySign);
		database.insert(SETTINGS_DATABASE_TABLE, null, cv);
	}
	
	public boolean verifyPassword(String password){
		Cursor c = database.rawQuery("SELECT * FROM settings WHERE FieldName='PASSWORD'", null);
		c.moveToFirst();
		boolean result=false;
		if(c.isAfterLast()==false){
			String res = c.getString(1);
			System.out.println(res);
			
			if(password.equals(res)){
				result=true;
			}else{
				result=false;
			}
		}
		System.out.println(result+"");
		return result;
	}
	
	public void updateIncome(int rowID,String source,double amount){
		ContentValues cv = new ContentValues();
			cv.put(INCOME_SOURCE,source);
			cv.put(INCOME_AMOUNT, amount);
		
		database.update(INCOME_DATABASE_TABLE,cv,KEY_ROWID+"=?",new String[]{rowID+""});
	}
	
	public void updateExpense(int rowID,String description,double amount){
		ContentValues cv = new ContentValues();
			cv.put(EXPENSE_DESCRIPTION,description);
			cv.put(EXPENSE_AMOUNT, amount);
		
		database.update(EXPENSE_DATABASE_TABLE,cv,KEY_ROWID+"=?",new String[]{rowID+""});
	}
	
	public void updatePassword(String newPassword){
		//database.execSQL("UPDATE FieldValue='"+newPassword+"' FROM settings WHERE FieldName='PASSWORD'");
		ContentValues cv = new ContentValues();
		cv.put("FieldValue", newPassword);
		
		database.update(SETTINGS_DATABASE_TABLE, cv , "FieldName=?", new String[]{"PASSWORD"});
	}
	
	public void updateCurrency(String newCurrencySign){
		//database.execSQL("UPDATE FieldValue='"+newPassword+"' FROM settings WHERE FieldName='PASSWORD'");
		ContentValues cv = new ContentValues();
		cv.put("FieldValue", newCurrencySign);
		
		database.update(SETTINGS_DATABASE_TABLE, cv , "FieldName=?", new String[]{"CURRENCYSIGN"});
	}
	
	public String getCurrencySign(){
		String sign="";
		Cursor c = database.rawQuery("SELECT * FROM settings WHERE FieldName='CURRENCYSIGN'", null);
		c.moveToFirst();
		if(c.isAfterLast()==false){
			sign=c.getString(1);
		}
		return sign;
	}
	
	public Cursor getExpenseByRowID(int rowID){
		Cursor c = database.rawQuery("SELECT * FROM "+EXPENSE_DATABASE_TABLE+" WHERE rowID='"+rowID+"'", null);
		return c;
	}
	
	public Cursor getIncomeByRowID(int rowID){
		Cursor c = database.rawQuery("SELECT * FROM "+INCOME_DATABASE_TABLE+" WHERE rowID='"+rowID+"'", null);
		return c;
	}
	/**
	 * Create a new todo If the todo is successfully created return the new
	 * rowId for that note, otherwise return a -1 to indicate failure.
	 */
	public long addIncome(String source, double amount) {
		ContentValues initialValues = createIncomeContentValues(source, amount);

		return database.insert(INCOME_DATABASE_TABLE, null, initialValues);
	}
	
	public long addExpense(String itemDescription, double amount) {
		ContentValues initialValues = createExpenseContentValues(itemDescription, amount);
		return database.insert(EXPENSE_DATABASE_TABLE, null, initialValues);
	}
//	private static final String INCOME_DATABASE_CREATE = "create table incomeData (_id integer primary key autoincrement, "
//		+ "dt text not null, source text not null, amount integer not null);";
//private static final String EXPENSE_DATABASE_CREATE = "create table expenseData (_id integer primary key autoincrement, "
//	+ "dt text not null, description text not null, amount integer not null);";
	private static final String INCOME_DATABASE_CREATE = "create table incomeData (_id integer primary key autoincrement, "
		+ "dt integer not null, month integer not null, year integer not null, source text not null, amount integer not null);";
private static final String EXPENSE_DATABASE_CREATE = "create table expenseData (_id integer primary key autoincrement, "
	+ "dt integer not null, month integer not null, year integer not null, description text not null, amount integer not null);";
private static final String SETTINGS_DATABASE_CREATE = "create table settings (FieldName text not null, FieldValue text not null);";

	public void resetDB(){
		database.execSQL("DROP incomeData");
		database.execSQL("DROP expenseData");
		database.execSQL("DROP settings");
		database.execSQL(INCOME_DATABASE_CREATE);
		database.execSQL(EXPENSE_DATABASE_CREATE);
		database.execSQL(SETTINGS_DATABASE_CREATE);
		ContentValues cv = new ContentValues();
		cv.put(SETTINGS_FIELDNAME, "PASSWORD");
		cv.put(SETTINGS_FIELDVALUE, "");
		database.insert(SETTINGS_DATABASE_TABLE, null, cv);
		cv = new ContentValues();
		cv.put(SETTINGS_FIELDNAME, "CURRENCYSIGN");
		cv.put(SETTINGS_FIELDVALUE, "$");
		database.insert(SETTINGS_DATABASE_TABLE, null, cv);
//		database.execSQL(INCOME_DATABASE_CREATE);
//		database.execSQL(EXPENSE_DATABASE_CREATE);
	}
	
	public boolean deleteIncome(int rowId) {
		return database.delete(INCOME_DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public boolean deleteExpense(int rowId) {
		return database.delete(EXPENSE_DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}
	/**
	 * Return a Cursor over the list of all todo in the database
	 * 
	 * @return Cursor over all notes
	 */
	public Cursor fetchAllIncome() {
		return database.query(INCOME_DATABASE_TABLE, new String[] { KEY_ROWID, INCOME_DT,
				INCOME_MONTH, INCOME_YEAR, INCOME_SOURCE, INCOME_AMOUNT }, null, null, null,
				null, null);
	}
	
	public Cursor fetchAllIncomeSources() {
		return database.rawQuery("SELECT DISTINCT "+INCOME_SOURCE+" FROM "+INCOME_DATABASE_TABLE, null);
	}
	
	public Cursor fetchAllItems() {
		return database.rawQuery("SELECT DISTINCT "+EXPENSE_DESCRIPTION+" FROM "+EXPENSE_DATABASE_TABLE, null);
	}
	
	public Cursor fetchTotalIncomeThisMonth() {
		int thisMonth = Calendar.getInstance().get(Calendar.MONTH)+1;
		int thisYear = Calendar.getInstance().get(Calendar.YEAR);
		
		return database.rawQuery("SELECT SUM("+INCOME_AMOUNT+") AS TotalAmount FROM "+INCOME_DATABASE_TABLE+" WHERE month='"+thisMonth+"' AND year='"+thisYear+"'", null);
	}
	
	public Cursor fetchTotalIncomeLastMonth() {
		
		int thisMonth = Calendar.getInstance().get(Calendar.MONTH)+1;
		thisMonth--;
		int thisYear = Calendar.getInstance().get(Calendar.YEAR);
		if(thisMonth==1){
			thisMonth=12;
			thisYear-=1;
	}
		
		return database.rawQuery("SELECT SUM("+INCOME_AMOUNT+") AS TotalAmount FROM "+INCOME_DATABASE_TABLE+" WHERE month='"+thisMonth+"' AND year='"+thisYear+"'", null);
	}
	
	public Cursor fetchTotalExpenseThisMonth() {
		int thisMonth = Calendar.getInstance().get(Calendar.MONTH)+1;
		int thisYear = Calendar.getInstance().get(Calendar.YEAR);
		
		return database.rawQuery("SELECT SUM("+EXPENSE_AMOUNT+") AS TotalAmount FROM "+EXPENSE_DATABASE_TABLE+" WHERE month='"+thisMonth+"' AND year='"+thisYear+"'", null);
	}
	
	public Cursor fetchTotalExpenseLastMonth() {
		
		int thisMonth = Calendar.getInstance().get(Calendar.MONTH);
		
		int thisYear = Calendar.getInstance().get(Calendar.YEAR);
		if(thisMonth==0){
			thisMonth=12;
			thisYear--;
	}
		
		return database.rawQuery("SELECT SUM("+EXPENSE_AMOUNT+") AS TotalAmount FROM "+EXPENSE_DATABASE_TABLE+" WHERE month='"+thisMonth+"' AND year='"+thisYear+"'", null);
	}
	
	public Cursor fetchIncomeByMonth(int month, int year) {
		return database.rawQuery("SELECT * FROM "+INCOME_DATABASE_TABLE + " WHERE month='"+month+"' AND year='"+year+"'", null);
	}
	
	public Cursor fetchExpenseByMonth(int month, int year) {
		return database.rawQuery("SELECT * FROM "+EXPENSE_DATABASE_TABLE + " WHERE month='"+month+"' AND year='"+year+"'", null);
	}
	
	public Cursor fetchIncomeBySource(String source) {
		return database.rawQuery("SELECT * FROM "+INCOME_DATABASE_TABLE + " WHERE source='"+source+"'", null);
	}
	
	public Cursor fetchExpenseByItem(String item) {
		return database.rawQuery("SELECT * FROM "+EXPENSE_DATABASE_TABLE + " WHERE description='"+item+"'", null);
	}
	
	public Cursor fetchAllExpense() {
		return database.query(EXPENSE_DATABASE_TABLE, new String[] { KEY_ROWID, EXPENSE_DT,
				EXPENSE_MONTH, EXPENSE_YEAR, EXPENSE_DESCRIPTION, EXPENSE_AMOUNT }, null, null, null,
				null, null);
	}

	/**
	 * Return a Cursor positioned at the defined todo
	 */
	public Cursor fetchIncome(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, INCOME_DATABASE_TABLE, new String[] {
				KEY_ROWID, INCOME_SOURCE, INCOME_AMOUNT },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	
	
	public Cursor fetchExpense(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, EXPENSE_DATABASE_TABLE, new String[] {
				KEY_ROWID, EXPENSE_DESCRIPTION, EXPENSE_AMOUNT },
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	private ContentValues createIncomeContentValues(String source, double amount) {
		ContentValues values = new ContentValues();
		Date dt = new Date();
		DecimalFormat dateFormat = new DecimalFormat("00");
		DecimalFormat monthFormat = new DecimalFormat("00");
		String date = dateFormat.format(Calendar.getInstance().get(Calendar.DATE));
		String month = monthFormat.format((Calendar.getInstance().get(Calendar.MONTH)+1));
		String year = Calendar.getInstance().get(Calendar.YEAR)+"";
		//String yr=year.substring(0, 2);
		values.put(INCOME_DT,date);
		values.put(INCOME_MONTH, month);
		values.put(INCOME_YEAR,year);
		values.put(INCOME_SOURCE, source);
		values.put(INCOME_AMOUNT, amount);
		//values.put(KEY_DESCRIPTION, description);
		return values;
	}
	private ContentValues createExpenseContentValues(String itemDescription, double amount) {
		ContentValues values = new ContentValues();
		Date dt = new Date();
		DecimalFormat dateFormat = new DecimalFormat("00");
		DecimalFormat monthFormat = new DecimalFormat("00");
		String date = dateFormat.format(Calendar.getInstance().get(Calendar.DATE));
		String month = monthFormat.format((Calendar.getInstance().get(Calendar.MONTH)+1));
		String year = Calendar.getInstance().get(Calendar.YEAR)+"";
		values.put(EXPENSE_DT,date);
		values.put(EXPENSE_MONTH, month);
		values.put(EXPENSE_YEAR,year);
		values.put(EXPENSE_DESCRIPTION, itemDescription);
		values.put(EXPENSE_AMOUNT, amount);
		//values.put(KEY_DESCRIPTION, description);
		return values;
	}
}